package com.wyk.project.service.impl;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.date.DateField;
import cn.hutool.core.date.DateUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.wyk.project.common.convention.exception.ClientException;
import com.wyk.project.dao.entity.*;
import com.wyk.project.dao.mapper.*;
import com.wyk.project.dto.req.ShortLinkGroupStatsAccessRecordReqDTO;
import com.wyk.project.dto.req.ShortLinkGroupStatsReqDTO;
import com.wyk.project.dto.req.ShortLinkStatsAccessRecordReqDTO;
import com.wyk.project.dto.req.ShortLinkStatsReqDTO;
import com.wyk.project.dto.resp.*;
import com.wyk.project.excel.ExportExcelUtil;
import com.wyk.project.excel.StatisticInfoModelExcel;
import com.wyk.project.service.ShortLinkStatsService;
import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Service;

import java.time.LocalDate;
import java.time.ZoneId;
import java.util.*;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;

@Service
@RequiredArgsConstructor
public class ShortLinkStatsServiceImpl implements ShortLinkStatsService {

    private final LinkAccessStatsMapper linkAccessStatsMapper;

    private final LinkLocaleStatsMapper linkLocaleStatsMapper;

    private final LinkAccessLogsStatsMapper linkAccessLogsMapper;

    private final LinkBrowserStatsMapper linkBrowserStatsMapper;

    private final LinkOsStatsMapper linkOsStatsMapper;

    private final LinkDeviceStatsMapper linkDeviceStatsMapper;

    private final LinkNetworkStatsMapper linkNetworkStatsMapper;

    private final ShortLinkMapper shortLinkMapper;

    private final LinkStatsTodayMapper linkStatsTodayMapper;



    /**
     *   访问单个短链接指定时间内监控
     * @param requestParam
     * @return
     */
    @Override
    public ShortLinkStatsRespDTO oneShortLinkStats(ShortLinkStatsReqDTO requestParam) {
        //一个短链接 不同日期下的 PV UV UIP
        // ps: 那一天，这个链接多少   pv，uv，uip
        // listStatsByShortLink= {4-16,pv:6,uv:2,uip:1;
        //                        4-17,pv:3,uv:0,uip:0;}
        //
        List<LinkAccessStatsDO> listStatsByShortLink = linkAccessStatsMapper.listStatsByShortLink(requestParam);
        if (CollUtil.isEmpty(listStatsByShortLink)) {
            return null;
        }
        // 基础访问数据 : 根据短链接获取指定日期内PV、UV、UIP总数据
        // 这是t_link和t_link_access_logs(每点一次短连接就记录一次)内连接查询，
        //      ps:查询4-16到4-17这段时间内，总点击的pv ，独立的uv，uip
        //pvUvUidStatsByShortLink = {pv:9,uv:2,uip:1}
        LinkAccessStatsDO pvUvUidStatsByShortLink = linkAccessLogsMapper.findPvUvUidStatsByShortLink(requestParam);
        // 基础访问详情 : 每一天的 PV UV UIP 数据
        List<ShortLinkStatsAccessDailyRespDTO> daily = new ArrayList<>();
        //将开始日期，到结束日期 变成如此 ["2024-03-21", "2024-03-22", "2024-03-23", "2024-03-24", "2024-03-25"]
        List<String> rangeDates = DateUtil.rangeToList(DateUtil.parse(requestParam.getStartDate()), DateUtil.parse(requestParam.getEndDate()), DateField.DAY_OF_MONTH).stream()
                .map(DateUtil::formatDate)
                .toList();
        // 开始查到的数据日期，和上一步的 日期列表对应，日期能对上，记录该天的 date,pv,uv,uip 进入ShortLinkStatsAccessDailyRespDTO
        //                                       日期对不上，该天的 数据为 0 0 0
        // 因为开始查到的数据日期，可能某一天没人访问   而rangeDates的日期是连续的
        rangeDates.forEach(each -> listStatsByShortLink.stream()
                .filter(item -> Objects.equals(each, DateUtil.formatDate(item.getDate())))
                .findFirst()
                .ifPresentOrElse(item -> {
                    ShortLinkStatsAccessDailyRespDTO accessDailyRespDTO = ShortLinkStatsAccessDailyRespDTO.builder()
                            .date(each)
                            .pv(item.getPv())
                            .uv(item.getUv())
                            .uip(item.getUip())
                            .build();
                    daily.add(accessDailyRespDTO);
                }, () -> {
                    ShortLinkStatsAccessDailyRespDTO accessDailyRespDTO = ShortLinkStatsAccessDailyRespDTO.builder()
                            .date(each)
                            .pv(0)
                            .uv(0)
                            .uip(0)
                            .build();
                    daily.add(accessDailyRespDTO);
                }));
        // 地区访问详情（仅国内） ！！！！！
        List<ShortLinkStatsLocaleCNRespDTO> localeCnStats = new ArrayList<>();
        // ---查的数据  province，cnt
        List<LinkLocaleStatsDO> listedLocaleByShortLink = linkLocaleStatsMapper.listLocaleByShortLink(requestParam);
        // ---拿到 所有province的cnt之和
        int localeCnSum = listedLocaleByShortLink.stream()
                .mapToInt(LinkLocaleStatsDO::getCnt)
                .sum();
        listedLocaleByShortLink.forEach(each -> {
            double ratio = (double) each.getCnt() / localeCnSum;
            double actualRatio = Math.round(ratio * 100.0) / 100.0;
            ShortLinkStatsLocaleCNRespDTO localeCNRespDTO = ShortLinkStatsLocaleCNRespDTO.builder()
                    .cnt(each.getCnt())
                    .locale(each.getProvince())
                    .ratio(actualRatio)
                    .build();
            localeCnStats.add(localeCNRespDTO);
        });
        // 小时访问详情 ！！！！！
        List<Integer> hourStats = new ArrayList<>();
        // ---查到的是 hour，pv
        //ps:  在4-16到4-17 我们一般再几点访问，次数是多少
        List<LinkAccessStatsDO> listHourStatsByShortLink = linkAccessStatsMapper.listHourStatsByShortLink(requestParam);
        // ---该小时内有数据，就把查到的pv 加入 hourStats ，没有就设置pv： 0
        for (int i = 0; i < 24; i++) {
            AtomicInteger hour = new AtomicInteger(i);
            int hourCnt = listHourStatsByShortLink.stream()
                    .filter(each -> Objects.equals(each.getHour(), hour.get()))
                    .findFirst()
                    .map(LinkAccessStatsDO::getPv)
                    .orElse(0);
            hourStats.add(hourCnt);
        }
        // 高频访问IP详情
        List<ShortLinkStatsTopIpRespDTO> topIpStats = new ArrayList<>();
        // ---log表 查 ip 和 该ip出现的总次数
        List<HashMap<String, Object>> listTopIpByShortLink = linkAccessLogsMapper.listTopIpByShortLink(requestParam);
        listTopIpByShortLink.forEach(each -> {
            ShortLinkStatsTopIpRespDTO statsTopIpRespDTO = ShortLinkStatsTopIpRespDTO.builder()
                    .ip(each.get("ip").toString())
                    .cnt(Integer.parseInt(each.get("count").toString()))
                    .build();
            topIpStats.add(statsTopIpRespDTO);
        });
        // 一周访问详情
        List<Integer> weekdayStats = new ArrayList<>();
        // --- 查到的是 日期内 存在访问 week 和 week时访问的总次数 pv
        List<LinkAccessStatsDO> listWeekdayStatsByShortLink = linkAccessStatsMapper.listWeekdayStatsByShortLink(requestParam);
        for (int i = 1; i < 8; i++) {
            AtomicInteger weekday = new AtomicInteger(i);
            int weekdayCnt = listWeekdayStatsByShortLink.stream()
                    .filter(each -> Objects.equals(each.getWeekday(), weekday.get()))
                    .findFirst()
                    .map(LinkAccessStatsDO::getPv)
                    .orElse(0);
            weekdayStats.add(weekdayCnt);
        }
        // 浏览器访问详情
        List<ShortLinkStatsBrowserRespDTO> browserStats = new ArrayList<>();
        // --- 给定的日期内 访问的浏览器类型 和 对应的访问次数
        List<HashMap<String, Object>> listBrowserStatsByShortLink = linkBrowserStatsMapper.listBrowserStatsByShortLink(requestParam);
        int browserSum = listBrowserStatsByShortLink.stream()
                .mapToInt(each -> Integer.parseInt(each.get("count").toString()))
                .sum();
        listBrowserStatsByShortLink.forEach(each -> {
            double ratio = (double) Integer.parseInt(each.get("count").toString()) / browserSum;
            double actualRatio = Math.round(ratio * 100.0) / 100.0;
            ShortLinkStatsBrowserRespDTO browserRespDTO = ShortLinkStatsBrowserRespDTO.builder()
                    .cnt(Integer.parseInt(each.get("count").toString()))
                    .browser(each.get("browser").toString())
                    .ratio(actualRatio)
                    .build();
            browserStats.add(browserRespDTO);
        });
        // 操作系统访问详情
        List<ShortLinkStatsOsRespDTO> osStats = new ArrayList<>();
        // --- 给定的日期内 访问的操作系统类型 和 对应的访问次数
        List<HashMap<String, Object>> listOsStatsByShortLink = linkOsStatsMapper.listOsStatsByShortLink(requestParam);
        int osSum = listOsStatsByShortLink.stream()
                .mapToInt(each -> Integer.parseInt(each.get("count").toString()))
                .sum();
        listOsStatsByShortLink.forEach(each -> {
            double ratio = (double) Integer.parseInt(each.get("count").toString()) / osSum;
            double actualRatio = Math.round(ratio * 100.0) / 100.0;
            ShortLinkStatsOsRespDTO osRespDTO = ShortLinkStatsOsRespDTO.builder()
                    .cnt(Integer.parseInt(each.get("count").toString()))
                    .os(each.get("os").toString())
                    .ratio(actualRatio)
                    .build();
            osStats.add(osRespDTO);
        });
        // 访客访问类型详情
        //  这个sql语句我有点迷 ----------回头再看一下  TODO
        List<ShortLinkStatsUvRespDTO> uvTypeStats = new ArrayList<>();
        HashMap<String, Object> findUvTypeByShortLink = linkAccessLogsMapper.findUvTypeCntByShortLink(requestParam);
        int oldUserCnt = Integer.parseInt(
                Optional.ofNullable(findUvTypeByShortLink)
                        .map(each -> each.get("oldUserCnt"))
                        .map(Object::toString)
                        .orElse("0")
        );
        int newUserCnt = Integer.parseInt(
                Optional.ofNullable(findUvTypeByShortLink)
                        .map(each -> each.get("newUserCnt"))
                        .map(Object::toString)
                        .orElse("0")
        );
        int uvSum = oldUserCnt + newUserCnt;
        double oldRatio = (double) oldUserCnt / uvSum;
        double actualOldRatio = Math.round(oldRatio * 100.0) / 100.0;
        double newRatio = (double) newUserCnt / uvSum;
        double actualNewRatio = Math.round(newRatio * 100.0) / 100.0;
        ShortLinkStatsUvRespDTO newUvRespDTO = ShortLinkStatsUvRespDTO.builder()
                .uvType("newUser")
                .cnt(newUserCnt)
                .ratio(actualNewRatio)
                .build();
        uvTypeStats.add(newUvRespDTO);
        ShortLinkStatsUvRespDTO oldUvRespDTO = ShortLinkStatsUvRespDTO.builder()
                .uvType("oldUser")
                .cnt(oldUserCnt)
                .ratio(actualOldRatio)
                .build();
        uvTypeStats.add(oldUvRespDTO);
        // 访问设备类型详情
        // --- 给定的日期内 访问的设备类型 和 对应的访问次数
        List<ShortLinkStatsDeviceRespDTO> deviceStats = new ArrayList<>();
        List<LinkDeviceStatsDO> listDeviceStatsByShortLink = linkDeviceStatsMapper.listDeviceStatsByShortLink(requestParam);
        int deviceSum = listDeviceStatsByShortLink.stream()
                .mapToInt(LinkDeviceStatsDO::getCnt)
                .sum();
        listDeviceStatsByShortLink.forEach(each -> {
            double ratio = (double) each.getCnt() / deviceSum;
            double actualRatio = Math.round(ratio * 100.0) / 100.0;
            ShortLinkStatsDeviceRespDTO deviceRespDTO = ShortLinkStatsDeviceRespDTO.builder()
                    .cnt(each.getCnt())
                    .device(each.getDevice())
                    .ratio(actualRatio)
                    .build();
            deviceStats.add(deviceRespDTO);
        });
        // 访问网络类型详情
        List<ShortLinkStatsNetworkRespDTO> networkStats = new ArrayList<>();
        List<LinkNetworkStatsDO> listNetworkStatsByShortLink = linkNetworkStatsMapper.listNetworkStatsByShortLink(requestParam);
        int networkSum = listNetworkStatsByShortLink.stream()
                .mapToInt(LinkNetworkStatsDO::getCnt)
                .sum();
        listNetworkStatsByShortLink.forEach(each -> {
            double ratio = (double) each.getCnt() / networkSum;
            double actualRatio = Math.round(ratio * 100.0) / 100.0;
            ShortLinkStatsNetworkRespDTO networkRespDTO = ShortLinkStatsNetworkRespDTO.builder()
                    .cnt(each.getCnt())
                    .network(each.getNetwork())
                    .ratio(actualRatio)
                    .build();
            networkStats.add(networkRespDTO);
        });
        return ShortLinkStatsRespDTO.builder()
                .pv(pvUvUidStatsByShortLink.getPv())
                .uv(pvUvUidStatsByShortLink.getUv())
                .uip(pvUvUidStatsByShortLink.getUip())
                .daily(daily)
                .localeCnStats(localeCnStats)
                .hourStats(hourStats)
                .topIpStats(topIpStats)
                .weekdayStats(weekdayStats)
                .browserStats(browserStats)
                .osStats(osStats)
                .uvTypeStats(uvTypeStats)
                .deviceStats(deviceStats)
                .networkStats(networkStats)
                .build();
    }

    /**
     *  访问单个短链接指定时间内 访问记录监控数据
     * @param requestParam
     * @return
     */
    @Override
    public IPage<ShortLinkStatsAccessRecordRespDTO> oneShortLinkStatsAccessRecord(ShortLinkStatsAccessRecordReqDTO requestParam) {
        LambdaQueryWrapper<LinkAccessLogsDO> queryWrapper = Wrappers.lambdaQuery(LinkAccessLogsDO.class)
                .eq(LinkAccessLogsDO::getFullShortUrl, requestParam.getFullShortUrl())
                .between(LinkAccessLogsDO::getCreateTime,requestParam.getStartDate(),requestParam.getEndDate())
                .eq(LinkAccessLogsDO::getDelFlag,0)
                .orderByDesc(LinkAccessLogsDO::getCreateTime);
        IPage<LinkAccessLogsDO> result = linkAccessLogsMapper.selectPage(requestParam, queryWrapper);
        //分页数据转化为我们想要的格式
        IPage<ShortLinkStatsAccessRecordRespDTO> actualResult = result.convert(each -> BeanUtil.toBean(each, ShortLinkStatsAccessRecordRespDTO.class));
        // 从日志中拿到 userlist
        List<String> userAccessLogsList = actualResult.getRecords()
                .stream()
                .map(ShortLinkStatsAccessRecordRespDTO::getUser)
                .toList();
        if (CollectionUtil.isEmpty(userAccessLogsList)) {
            return actualResult;
        }
        // 查用户类型 放的是 "user的code" ："新/老访客"
        List<Map<String,Object>> UvTypeList = linkAccessLogsMapper.selectUvTypeByUsers(
                requestParam.getGid(),
                requestParam.getFullShortUrl(),
                requestParam.getEnableStatus(),
                requestParam.getStartDate(),
                requestParam.getEndDate(),
                userAccessLogsList);
        actualResult.getRecords().forEach(each -> {
            String uvType = UvTypeList.stream()
                    .filter(item -> Objects.equals(item.get("user"), each.getUser()))
                    .findFirst()
                    .map(item -> item.get("uvType"))
                    .map(Object::toString)
                    .orElse("旧访客");
            each.setUvType(uvType);
        });
        return actualResult;

    }

    /**
     * 访问分组短链接指定时间内监控数据
     * @param requestParam
     * @return
     */
    @Override
    public ShortLinkStatsRespDTO groupShortLinkStats(ShortLinkGroupStatsReqDTO requestParam) {
        List<LinkAccessStatsDO> listStatsByGroup = linkAccessStatsMapper.listStatsByGroup(requestParam);
        if (CollUtil.isEmpty(listStatsByGroup)) {
            return null;
        }
        // 基础访问数据
        LinkAccessStatsDO pvUvUidStatsByGroup = linkAccessLogsMapper.findPvUvUidStatsByGroup(requestParam);
        // 基础访问详情
        List<ShortLinkStatsAccessDailyRespDTO> daily = new ArrayList<>();
        List<String> rangeDates = DateUtil.rangeToList(DateUtil.parse(requestParam.getStartDate()), DateUtil.parse(requestParam.getEndDate()), DateField.DAY_OF_MONTH).stream()
                .map(DateUtil::formatDate)
                .toList();
        rangeDates.forEach(each -> listStatsByGroup.stream()
                .filter(item -> Objects.equals(each, DateUtil.formatDate(item.getDate())))
                .findFirst()
                .ifPresentOrElse(item -> {
                    ShortLinkStatsAccessDailyRespDTO accessDailyRespDTO = ShortLinkStatsAccessDailyRespDTO.builder()
                            .date(each)
                            .pv(item.getPv())
                            .uv(item.getUv())
                            .uip(item.getUip())
                            .build();
                    daily.add(accessDailyRespDTO);
                }, () -> {
                    ShortLinkStatsAccessDailyRespDTO accessDailyRespDTO = ShortLinkStatsAccessDailyRespDTO.builder()
                            .date(each)
                            .pv(0)
                            .uv(0)
                            .uip(0)
                            .build();
                    daily.add(accessDailyRespDTO);
                }));
        // 地区访问详情（仅国内）
        List<ShortLinkStatsLocaleCNRespDTO> localeCnStats = new ArrayList<>();
        List<LinkLocaleStatsDO> listedLocaleByGroup = linkLocaleStatsMapper.listLocaleByGroup(requestParam);
        int localeCnSum = listedLocaleByGroup.stream()
                .mapToInt(LinkLocaleStatsDO::getCnt)
                .sum();
        listedLocaleByGroup.forEach(each -> {
            double ratio = (double) each.getCnt() / localeCnSum;
            double actualRatio = Math.round(ratio * 100.0) / 100.0;
            ShortLinkStatsLocaleCNRespDTO localeCNRespDTO = ShortLinkStatsLocaleCNRespDTO.builder()
                    .cnt(each.getCnt())
                    .locale(each.getProvince())
                    .ratio(actualRatio)
                    .build();
            localeCnStats.add(localeCNRespDTO);
        });
        // 小时访问详情
        List<Integer> hourStats = new ArrayList<>();
        List<LinkAccessStatsDO> listHourStatsByGroup = linkAccessStatsMapper.listHourStatsByGroup(requestParam);
        for (int i = 0; i < 24; i++) {
            AtomicInteger hour = new AtomicInteger(i);
            int hourCnt = listHourStatsByGroup.stream()
                    .filter(each -> Objects.equals(each.getHour(), hour.get()))
                    .findFirst()
                    .map(LinkAccessStatsDO::getPv)
                    .orElse(0);
            hourStats.add(hourCnt);
        }
        // 高频访问IP详情
        List<ShortLinkStatsTopIpRespDTO> topIpStats = new ArrayList<>();
        List<HashMap<String, Object>> listTopIpByGroup = linkAccessLogsMapper.listTopIpByGroup(requestParam);
        listTopIpByGroup.forEach(each -> {
            ShortLinkStatsTopIpRespDTO statsTopIpRespDTO = ShortLinkStatsTopIpRespDTO.builder()
                    .ip(each.get("ip").toString())
                    .cnt(Integer.parseInt(each.get("count").toString()))
                    .build();
            topIpStats.add(statsTopIpRespDTO);
        });
        // 一周访问详情
        List<Integer> weekdayStats = new ArrayList<>();
        List<LinkAccessStatsDO> listWeekdayStatsByGroup = linkAccessStatsMapper.listWeekdayStatsByGroup(requestParam);
        for (int i = 1; i < 8; i++) {
            AtomicInteger weekday = new AtomicInteger(i);
            int weekdayCnt = listWeekdayStatsByGroup.stream()
                    .filter(each -> Objects.equals(each.getWeekday(), weekday.get()))
                    .findFirst()
                    .map(LinkAccessStatsDO::getPv)
                    .orElse(0);
            weekdayStats.add(weekdayCnt);
        }
        // 浏览器访问详情
        List<ShortLinkStatsBrowserRespDTO> browserStats = new ArrayList<>();
        List<HashMap<String, Object>> listBrowserStatsByGroup = linkBrowserStatsMapper.listBrowserStatsByGroup(requestParam);
        int browserSum = listBrowserStatsByGroup.stream()
                .mapToInt(each -> Integer.parseInt(each.get("count").toString()))
                .sum();
        listBrowserStatsByGroup.forEach(each -> {
            double ratio = (double) Integer.parseInt(each.get("count").toString()) / browserSum;
            double actualRatio = Math.round(ratio * 100.0) / 100.0;
            ShortLinkStatsBrowserRespDTO browserRespDTO = ShortLinkStatsBrowserRespDTO.builder()
                    .cnt(Integer.parseInt(each.get("count").toString()))
                    .browser(each.get("browser").toString())
                    .ratio(actualRatio)
                    .build();
            browserStats.add(browserRespDTO);
        });
        // 操作系统访问详情
        List<ShortLinkStatsOsRespDTO> osStats = new ArrayList<>();
        List<HashMap<String, Object>> listOsStatsByGroup = linkOsStatsMapper.listOsStatsByGroup(requestParam);
        int osSum = listOsStatsByGroup.stream()
                .mapToInt(each -> Integer.parseInt(each.get("count").toString()))
                .sum();
        listOsStatsByGroup.forEach(each -> {
            double ratio = (double) Integer.parseInt(each.get("count").toString()) / osSum;
            double actualRatio = Math.round(ratio * 100.0) / 100.0;
            ShortLinkStatsOsRespDTO osRespDTO = ShortLinkStatsOsRespDTO.builder()
                    .cnt(Integer.parseInt(each.get("count").toString()))
                    .os(each.get("os").toString())
                    .ratio(actualRatio)
                    .build();
            osStats.add(osRespDTO);
        });
        // 访问设备类型详情
        List<ShortLinkStatsDeviceRespDTO> deviceStats = new ArrayList<>();
        List<LinkDeviceStatsDO> listDeviceStatsByGroup = linkDeviceStatsMapper.listDeviceStatsByGroup(requestParam);
        int deviceSum = listDeviceStatsByGroup.stream()
                .mapToInt(LinkDeviceStatsDO::getCnt)
                .sum();
        listDeviceStatsByGroup.forEach(each -> {
            double ratio = (double) each.getCnt() / deviceSum;
            double actualRatio = Math.round(ratio * 100.0) / 100.0;
            ShortLinkStatsDeviceRespDTO deviceRespDTO = ShortLinkStatsDeviceRespDTO.builder()
                    .cnt(each.getCnt())
                    .device(each.getDevice())
                    .ratio(actualRatio)
                    .build();
            deviceStats.add(deviceRespDTO);
        });
        // 访问网络类型详情
        List<ShortLinkStatsNetworkRespDTO> networkStats = new ArrayList<>();
        List<LinkNetworkStatsDO> listNetworkStatsByGroup = linkNetworkStatsMapper.listNetworkStatsByGroup(requestParam);
        int networkSum = listNetworkStatsByGroup.stream()
                .mapToInt(LinkNetworkStatsDO::getCnt)
                .sum();
        listNetworkStatsByGroup.forEach(each -> {
            double ratio = (double) each.getCnt() / networkSum;
            double actualRatio = Math.round(ratio * 100.0) / 100.0;
            ShortLinkStatsNetworkRespDTO networkRespDTO = ShortLinkStatsNetworkRespDTO.builder()
                    .cnt(each.getCnt())
                    .network(each.getNetwork())
                    .ratio(actualRatio)
                    .build();
            networkStats.add(networkRespDTO);
        });
        return ShortLinkStatsRespDTO.builder()
                .pv(pvUvUidStatsByGroup.getPv())
                .uv(pvUvUidStatsByGroup.getUv())
                .uip(pvUvUidStatsByGroup.getUip())
                .daily(daily)
                .localeCnStats(localeCnStats)
                .hourStats(hourStats)
                .topIpStats(topIpStats)
                .weekdayStats(weekdayStats)
                .browserStats(browserStats)
                .osStats(osStats)
                .deviceStats(deviceStats)
                .networkStats(networkStats)
                .build();
    }

    /**
     * 分组内指定时间内 访问 的历史记录
     * @param requestParam
     * @return
     */
    @Override
    public IPage<ShortLinkStatsAccessRecordRespDTO> groupShortLinkStatsAccessRecord(ShortLinkGroupStatsAccessRecordReqDTO requestParam) {
        IPage<LinkAccessLogsDO> linkAccessLogsDOIPage = linkAccessLogsMapper.selectGroupPage(requestParam);
        IPage<ShortLinkStatsAccessRecordRespDTO> actualResult = linkAccessLogsDOIPage
                .convert(each -> BeanUtil.toBean(each, ShortLinkStatsAccessRecordRespDTO.class));

        List<String> userAccessLogsList = actualResult.getRecords().stream()
                .map(ShortLinkStatsAccessRecordRespDTO::getUser)
                .toList();
        List<Map<String, Object>> uvTypeList = linkAccessLogsMapper.selectGroupUvTypeByUsers(
                requestParam.getGid(),
                requestParam.getStartDate(),
                requestParam.getEndDate(),
                userAccessLogsList
        );
        actualResult.getRecords().forEach(each -> {
            String uvType = uvTypeList.stream()
                    .filter(item -> Objects.equals(each.getUser(), item.get("user")))
                    .findFirst()
                    .map(item -> item.get("uvType"))
                    .map(Object::toString)
                    .orElse("旧访客");
            each.setUvType(uvType);
        });
        return actualResult;
    }

    /**
     * 导出分组短链接一周内内访问的历史记录
     * @param id 短连接 ID
     */
    @Override
    public String exportShortLinkStatsAccessRecord(Integer id) {
        QueryWrapper<ShortLinkDO> queryWrapper = new QueryWrapper<>();
        if (id == null) {
            throw new ClientException("ID 信息为空");
        }
        queryWrapper.eq("id",id);
        ShortLinkDO shortLinkDO = shortLinkMapper.selectOne(queryWrapper);
        if (shortLinkDO == null) {
            throw new ClientException("短连接不存在");
        }
        // 短连接地址
        String shortUri = shortLinkDO.getShortUri();
        // 短连接历史访问总数
        Integer totalPv = shortLinkDO.getTotalPv();
        // 短连接历史独立访客数
        Integer totalUv = shortLinkDO.getTotalUv();
        // 短连接所在分组
        String gid = shortLinkDO.getGid();
        // 短连接路径，用于下面查询链接的相关信息
        String fullShortUrl = shortLinkDO.getFullShortUrl();

        // 天数和短连接为唯一索引，记录点击情况的信息
        List<LinkStatsTodayDO> linkStatsTodayDOList = linkStatsTodayMapper.selectList(new QueryWrapper<LinkStatsTodayDO>().eq("full_short_url", fullShortUrl));

        // 短连接创建时间
        Date createTime = shortLinkDO.getCreateTime();
        Date now = new Date();
        long differenceInMillis = Math.abs(createTime.getTime() - now.getTime());
        // 拿到短连接创建的天数
        long differenceInDays = TimeUnit.DAYS.convert(differenceInMillis, TimeUnit.MILLISECONDS);

        // 获取当前时间
        LocalDate today = LocalDate.now();
        LocalDate oneWeekDate  = today.minusDays(7);
        // 过滤得到符合时间范围的数据
        List<LinkStatsTodayDO> linkStatsTodayDOS = linkStatsTodayDOList.stream().filter(param -> {
            LocalDate accessDate = param.getDate().toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
            // 筛选出日期在 oneWeekAgo 到 today 之间的数据
            return !accessDate.isBefore(oneWeekDate) && !accessDate.isAfter(today);
        }).toList();
        // 拿到短连接一周内 pv 总数
        long countPv = linkStatsTodayDOS.stream().mapToLong(LinkStatsTodayDO::getTodayPv).sum();
        // 拿到短连接一周内 uv 总数
        long uvCount = linkStatsTodayDOS.stream().mapToLong(LinkStatsTodayDO::getTodayUv).sum();
        // 这次拿到数据上周的数据，用于和这周的做对比
        LocalDate lastWeekDate = today.minusDays(14);
        List<LinkStatsTodayDO> lastLinkStatsTodayDOS = linkStatsTodayDOList.stream().filter(param -> {
            LocalDate accessDate = param.getDate().toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
            // 筛选出日期在 oneWeekAgo 到 today 之间的数据·
            return !accessDate.isBefore(lastWeekDate) && !accessDate.isAfter(oneWeekDate);
        }).toList();
        // 拿到上周的 pv 和 uv
        long lastCountPv = lastLinkStatsTodayDOS.stream().mapToLong(LinkStatsTodayDO::getTodayPv).sum();
        long lastUvCount = lastLinkStatsTodayDOS.stream().mapToLong(LinkStatsTodayDO::getTodayUv).sum();
        String increasePv = null;
        String increaseUv = null;
        // 求出 PV 增长的百分比
        if (lastCountPv == 0) {
            if (countPv == 0) {
                // 上周和本周点击量都为 0，增长率为 0%
                increasePv = "0%";
            } else {
                // 上周点击量为 0，本周有点击量，增长率视为无穷大
                increasePv = "∞%";
            }
        } else {
            double increaseRate = ((double) (countPv - lastCountPv) / lastCountPv) * 100;
            if (increaseRate >= 0) {
                // 增长率为正
                increasePv = String.format("%.2f%%", increaseRate);
            } else {
                // 增长率为负
                increasePv = String.format("-%.2f%%", Math.abs(increaseRate));
            }
        }
        // 求出 UV 增长的百分比
        if (lastUvCount == 0) {
            if (uvCount == 0) {
                // 上周和本周独立访客数都为 0，增长率为 0%
                increaseUv = "0%";
            } else {
                // 上周独立访客数为 0，本周有独立访客，增长率视为无穷大
                increaseUv = "∞%";
            }
        } else {
            // 计算增长率
            double increaseRate = ((double) (uvCount - lastUvCount) / lastUvCount) * 100;
            if (increaseRate >= 0) {
                // 增长率为正，保留两位小数并格式化为百分比形式
                increaseUv = String.format("%.2f%%", increaseRate);
            } else {
                // 增长率为负，取绝对值并保留两位小数，前面加上负号
                increaseUv = String.format("-%.2f%%", Math.abs(increaseRate));
            }
        }

        // 获取短连接地区监控信息
        List<LinkLocaleStatsDO> linkLocaleStatsDOList = linkLocaleStatsMapper.selectList(new QueryWrapper<LinkLocaleStatsDO>().eq("full_short_url", fullShortUrl));
        List<LinkLocaleStatsDO> linkLocaleStatsDOS = linkLocaleStatsDOList.stream().filter(data -> {
            LocalDate accessDate = data.getDate().toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
            // 筛选出日期在 oneWeekAgo 到 today 之间的数据
            return !accessDate.isBefore(oneWeekDate) && !accessDate.isAfter(today);
        }).toList();
        String top3ProvinceStr = "";
        if (CollectionUtil.isNotEmpty(linkLocaleStatsDOS)) {
            // 获取点击量排名前 3 的省份信息
            List<Map.Entry<String, Integer>> top3Provinces = linkLocaleStatsDOS.stream()
                    .collect(Collectors.groupingBy(LinkLocaleStatsDO::getProvince, Collectors.summingInt(LinkLocaleStatsDO::getCnt)))
                    .entrySet().stream()
                    .sorted(Map.Entry.<String, Integer>comparingByValue().reversed())
                    .limit(3).toList();
            top3ProvinceStr = String.join(",", top3Provinces.stream().map(Map.Entry::getKey).toList());
        }

        // 获取短连接时间监控信息
        List<LinkAccessStatsDO> linkAccessStatsDOList = linkAccessStatsMapper.selectList(new QueryWrapper<LinkAccessStatsDO>().eq("full_short_url", fullShortUrl));
        List<LinkAccessStatsDO> linkAccessStatsDOS = linkAccessStatsDOList.stream().filter(param -> {
            LocalDate accessDate = param.getDate().toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
            // 筛选出日期在 oneWeekAgo 到 today 之间的数据
            return !accessDate.isBefore(oneWeekDate) && !accessDate.isAfter(today);
        }).toList();
        String top3HourStr = "";
        if (CollectionUtil.isNotEmpty(linkAccessStatsDOS)) {
            List<Map.Entry<Integer, Integer>> entries = linkAccessStatsDOS.stream()
                    .collect(Collectors.groupingBy(LinkAccessStatsDO::getHour, Collectors.summingInt(LinkAccessStatsDO::getPv)))
                    .entrySet().stream()
                    .sorted(Map.Entry.<Integer, Integer>comparingByValue().reversed())
                    .limit(3).toList();
            top3HourStr = String.join(",", entries.stream().map(entry -> entry.getKey() + "点").toList());
        }

        // 组装数据导出报表
        StatisticInfoModelExcel statisticInfoModelExcel = new StatisticInfoModelExcel();
        statisticInfoModelExcel.setGroup(gid);
        statisticInfoModelExcel.setShortLinkUrl(shortUri);
        statisticInfoModelExcel.setTotalPv(String.valueOf(totalPv));
        statisticInfoModelExcel.setTotalUv(String.valueOf(totalUv));
        statisticInfoModelExcel.setWeekDayPv(String.valueOf(countPv));
        statisticInfoModelExcel.setLaseWeekDayPv(String.valueOf(lastCountPv));
        statisticInfoModelExcel.setIncreasePvRation(increasePv);
        statisticInfoModelExcel.setWeekDayUv(String.valueOf(uvCount));
        statisticInfoModelExcel.setLaseWeekDayUv(String.valueOf(lastUvCount));
        statisticInfoModelExcel.setIncreaseUvRation(increaseUv);
        statisticInfoModelExcel.setUserTime(differenceInDays + "天");
        statisticInfoModelExcel.setArea(top3ProvinceStr);
        statisticInfoModelExcel.setClickTime(top3HourStr);
        List <StatisticInfoModelExcel> list = new ArrayList<>();
        list.add(statisticInfoModelExcel);
        ExportExcelUtil.exportExcel(list,  gid + "-" +fullShortUrl.split("/")[1]);
        return "导出成功";
    }
}